package com.gack.business.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.gack.business.model.UserEnterpriseDepartmentPosition;
import com.gack.business.vo.EnterpriseItemVO;

/**
 * 
 * @author ws
 * 2018-5-30
 */
@Repository		  
public interface UserEnterpriseDepartmentPositionRepository extends JpaRepository<UserEnterpriseDepartmentPosition, String>, JpaSpecificationExecutor<UserEnterpriseDepartmentPosition>{

	@Query("select count(uedp.userId)"
			+ " from UserEnterpriseDepartmentPosition uedp"
			+ " where uedp.enterpriseId=:enterpriseId"
			+ " and (uedp.departmentId=null or uedp.departmentId='')")
	Integer findNoDepartmentNumByEnterpriseId(@Param("enterpriseId") String enterpriseId);//查询该公司下无部门人员数量
	
	
	@Modifying
	@Query("delete from UserEnterpriseDepartmentPosition where enterpriseId = :enterpriseId")
	Integer deleteByEnterpriseId(@Param("enterpriseId") String enterpriseid);
	
	@Modifying
	@Query(value = "update user_enterprise_department_position uedp set uedp.position_id = null, uedp.update_time=:date where uedp.position_id=:positionId", nativeQuery = true)
	int setNullPositionIdByPositionId(@Param("positionId") String positionId, @Param("date") Date date);//将相应人员的positionId置为空
	
	@Query("select count(uedp.id)"
			+ " from UserEnterpriseDepartmentPosition uedp"
			+ " where uedp.userId=:userId"
			+ " and uedp.enterpriseId=:enterpriseId")
	int countByUserIdAndEnterpriseId(@Param("userId") String userId, @Param("enterpriseId") String enterpriseId);//查询某个公司下某个用户是否存在
	
	List<UserEnterpriseDepartmentPosition> findByUserIdAndEnterpriseId(String userId, String enterpriseId);//根据userid和公司id查询对应记录
	
	@Query("select uedp.departmentId"
			+ " from UserEnterpriseDepartmentPosition uedp"
			+ " where uedp.enterpriseId=:enterpriseId"
			+ " and uedp.userId=:userId")
	String findDepartmentIdByEnterpriseIdAndUserId(@Param("enterpriseId") String enterpriseId, @Param("userId") String userId);//根据公司id和用户id查询部门id
	
	@Modifying
	@Query("update UserEnterpriseDepartmentPosition uedp set uedp.enterpriseRemarks=:enterpriseRemarks, uedp.updateTime=:date where uedp.enterpriseId=:enterpriseId and uedp.userId=:userId")
	int updateEnterpriseRemarks(@Param("enterpriseId") String enterpriseId, @Param("userId") String userId, @Param("enterpriseRemarks") String enterpriseRemarks, @Param("date") Date date);//更改某用户在某公司下企业备注

	@Query("select departmentId from UserEnterpriseDepartmentPosition where userId=:userId")
	List<String> findDepartmentIdsByUserId(@Param("userId") String userId);
	
	@Query("select enterpriseId from UserEnterpriseDepartmentPosition where userId=:userId")
	List<String> findEnterprisesIdsByUserId(@Param("userId") String userId);
	
	@Query("from UserEnterpriseDepartmentPosition uedp where uedp.enterpriseRemarks = null or uedp.enterpriseRemarks = ''")
	List<UserEnterpriseDepartmentPosition> test1();
	
	@Query("from UserEnterpriseDepartmentPosition uedp where uedp.enterpriseRemarks != null and uedp.enterpriseRemarks != ''")
	List<UserEnterpriseDepartmentPosition> test2();
	
	@Query("select enterpriseRemarks"
			+ " from UserEnterpriseDepartmentPosition"
			+ " where enterpriseId=:enterpriseId"
			+ " and userId=:userId")
	String findEnterpriseRemarks(@Param("userId") String userId, @Param("enterpriseId") String enterpriseId);//查询用户在公司下的企业备注
	
	@Query("select userId"
			+ " from UserEnterpriseDepartmentPosition"
			+ " where enterpriseId=:enterpriseId")
	List<String> findUserIdByEnterpriseId(@Param("enterpriseId") String enterpriseId);//查询公司下所有员工id
	
	@Query("select userId"
			+ " from UserEnterpriseDepartmentPosition"
			+ " where enterpriseId=:enterpriseId"
			+ " and positionId=:positionId")
	List<String> findUserIdByEnterpriseIdAndPositionId(@Param("enterpriseId") String enterpriseId, @Param("positionId") String positionId);
	
	@Query("select userId"
			+ " from UserEnterpriseDepartmentPosition"
			+ " where enterpriseId=:enterpriseId"
			+ " and departmentId=:departmentId")
	List<String> findUserIdByEnterpriseIdAndDepartmentId(@Param("enterpriseId") String enterpriseId, @Param("departmentId") String departmentId);
	
	@Query("select new com.gack.business.vo.EnterpriseItemVO(e.id, e.name, e.currentNumber, e.status)"
	+ " from Enterprise e"
	+ " where e.id in (select distinct uedp.enterpriseId"
						+ " from UserEnterpriseDepartmentPosition uedp"
						+ " where uedp.userId=:userId)"
	+ " and e.status != '4'"
	+ " order by e.createTime Desc")
	List<EnterpriseItemVO> findEnterpriseItemVOListByUserId(@Param("userId") String userId);//查询某用户所在的公司,并按创建时间,降序排序
	
	@Query("select username"
			+ " from User"
			+ " where id in (select userId"
					+ " from UserEnterpriseDepartmentPosition"
					+ " where enterpriseId=:enterpriseId)")
	List<String> findUsernameByEnterpriseId(@Param("enterpriseId")String enterpriseId);
	
	@Query(value = "select a.enterprise_id"
			+ " from user_enterprise_department_position a join enterprise b on a.enterprise_id = b.id"
			+ " where a.user_id=:userId"
			+ " and b.status != '4'"
			+ " order by b.create_time desc"
			+ " limit 1", nativeQuery = true)
	String findNewestCreatedEnterprise(@Param("userId")String userId);
}
